Client Report - Late Flights & Missing Data (JSON)

On this page

• Elevator pitch
• QUESTION|TASK 1
• QUESTION|TASK 2
• QUESTION|TASK 3
• QUESTION|TASK 4
• QUESTION|TASK 5
• STRETCH QUESTION|TASK 1

• Report an issue

import pandas as pd
import numpy as np
from lets_plot import *

LetsPlot.setup_html(isolated_frame=True)

# Learn morea about Code Cells: https://quarto.org/docs/reference/cells/cells-jupyter.html

# Include and execute your code here
df = pd.read_json("https://github.com/byuidatascience/data4missing/raw/master/data-raw/flights_missing/flights_missing.json")

Elevator pitch

I performed data analysis on flight delays to determine the most and elast reliable airports to travel out of. I also calculated the best month to avoid the most delays and what airports have the highest proportion of weather delays.

QUESTION|TASK 1

Fix all of the varied missing data types in the data to be consistent (all missing values should be displayed as “NaN”). In your report include one record example (one row) from your new data, in the raw JSON format. Your example should display the “NaN” for at least one missing value.__

Here I have cleaned up the data to replace all N/A values with NaN. Here is an example line from the data in JSON format with the NaN value.

df_clean = df.replace(["", "NA", -999, None], np.nan)

df_display = df_clean.fillna("NaN")

example_row = df_display[df_display.eq("NaN").any(axis=1)].iloc[[0]]

print(example_row.to_json(orient="records"))

[{"airport_code":"ATL","airport_name":"Atlanta, GA: Hartsfield-Jackson Atlanta International","month":"January","year":2005.0,"num_of_flights_total":35048,"num_of_delays_carrier":"1500+","num_of_delays_late_aircraft":"NaN","num_of_delays_nas":4598,"num_of_delays_security":10,"num_of_delays_weather":448,"num_of_delays_total":8355,"minutes_delayed_carrier":116423.0,"minutes_delayed_late_aircraft":104415,"minutes_delayed_nas":207467.0,"minutes_delayed_security":297,"minutes_delayed_weather":36931,"minutes_delayed_total":465533}]

QUESTION|TASK 2

Which airport has the worst delays? Describe the metric you chose, and why you chose it to determine the “worst” airport. Your answer should include a summary table that lists (for each airport) the total number of flights, total number of delayed flights, proportion of delayed flights, and average delay time in hours.

To determine the worst airport, I calculated two metrics that together show which airport is the worst. The two metrics are proportion of flights delayed (delayed_flights/total_flights) and average delay in hours. From this data, SFO is the worst airport in terms of the proportion of flights delayed, and it has the second worst average delay time.

df2 = df_clean.copy()

delay_min_cols = [
    "minutes_delayed_carrier", "minutes_delayed_late_aircraft",
    "minutes_delayed_nas",     "minutes_delayed_security",
    "minutes_delayed_weather"
]
df2["total_delay_mins"] = df2[delay_min_cols].sum(axis=1)

summary = (
    df2
      .groupby("airport_code")
      .agg(
         total_flights    = ("num_of_flights_total", "sum"),
         delayed_flights  = ("num_of_delays_total",   "sum"),
         total_delay_mins = ("total_delay_mins",      "sum"),
      )
      .assign(
         prop_delayed    = lambda d: d["delayed_flights"] / d["total_flights"],
         avg_delay_hours = lambda d: (d["total_delay_mins"] / d["delayed_flights"]) / 60
      )
      .reset_index()
)

print(summary.sort_values("prop_delayed", ascending=False))

  airport_code  total_flights  delayed_flights  total_delay_mins  \
5          SFO        1630945           425604        25855436.0   
3          ORD        3597588           830825        54600082.0   
0          ATL        4430047           902443        52771235.0   
2          IAD         851571           168467         9866735.0   
4          SAN         917862           175132         8046501.0   
1          DEN        2513974           468519        24563691.0   
6          SLC        1403384           205160         9825240.0   

   prop_delayed  avg_delay_hours  
5      0.260955         1.012500  
3      0.230939         1.095298  
0      0.203710         0.974600  
2      0.197831         0.976129  
4      0.190804         0.765756  
1      0.186366         0.873806  
6      0.146189         0.798177  

QUESTION|TASK 3

What is the best month to fly if you want to avoid delays of any length? Describe the metric you chose and why you chose it to calculate your answer. Include one chart to help support your answer, with the x-axis ordered by month. (To answer this question, you will need to remove any rows that are missing the Month variable.)

To determine the best month to fly, I used the same metric I used previously: the proportion of flights delayed to the total number of flights. The bar chart shows that September has the lowest proportion of delayed flights, and therefore it is the best month to fly.

df3 = df_clean.dropna(subset=["month"]).copy()

month_summary = (
    df3
      .groupby("month")
      .agg(
         total_flights   = ("num_of_flights_total", "sum"),
         delayed_flights = ("num_of_delays_total",   "sum"),
      )
      .assign(
         prop_delayed = lambda d: d["delayed_flights"] / d["total_flights"]
      )
      .reset_index()
)

month_order = [
    "January","February","March","April","May","June",
    "July","August","September","October","November","December"
]
month_summary["month"] = pd.Categorical(
    month_summary["month"],
    categories=month_order,
    ordered=True
)

best_month = month_summary.loc[month_summary["prop_delayed"].idxmin(), "month"]
print(f"Best month to fly: {best_month}")

from lets_plot import ggplot, aes, geom_bar, labs

p3 = (
    ggplot(month_summary, aes(x="month", y="prop_delayed"))
    + geom_bar(stat="identity")
    + labs(
        x="Month",
        y="Proportion of Flights Delayed",
        title="Flight Delay Proportion by Month"
    )
)

p3

Best month to fly: September

QUESTION|TASK 4

According to the BTS website, the “Weather” category only accounts for severe weather delays. Mild weather delays are not counted in the “Weather” category, but are actually included in both the “NAS” and “Late-Arriving Aircraft” categories. Your job is to create a new column that calculates the total number of flights delayed by weather (both severe and mild). You will need to replace all the missing values in the Late Aircraft variable with the mean. Show your work by printing the first 5 rows of data in a table. Use these three rules for your calculations:

a. 100% of delayed flights in the Weather category are due to weather  
a. 30% of all delayed flights in the Late-Arriving category are due to weather  
a. From April to August, 40% of delayed flights in the NAS category are due to weather. The rest of the months, the proportion rises to 65%    

For January at ATL, an average of 1,109 late-aircraft delays plus 4,598 NAS delays and 448 severe-weather delays combine into about 3,769 total weather-related delay counts under our assumptions. Across airports, ORD shows the highest weather-delay total (~4,502), while SAN has the lowest (~675).

df4 = df_clean.copy()
df4["month_num"] = pd.to_datetime(
    df4["month"], format="%B", errors="coerce"
).dt.month

for col in [
    "num_of_delays_late_aircraft",
    "num_of_delays_nas",
    "num_of_delays_weather"
]:
    df4[col] = pd.to_numeric(df4[col], errors="coerce")

mean_late = df4["num_of_delays_late_aircraft"].mean()
df4["num_of_delays_late_aircraft"] = df4["num_of_delays_late_aircraft"].fillna(mean_late)

df4["num_of_delays_nas"]     = df4["num_of_delays_nas"].fillna(0)
df4["num_of_delays_weather"] = df4["num_of_delays_weather"].fillna(0)

nas_prop = np.where(df4["month_num"].between(4, 8), 0.40, 0.65)

df4["weather_delay_count"] = (
    df4["num_of_delays_weather"]                     
  + df4["num_of_delays_late_aircraft"] * 0.30         
  + df4["num_of_delays_nas"] * nas_prop               
)

df4.loc[
    :5, 
    [
      "airport_code", "month", "month_num",
      "num_of_delays_weather",
      "num_of_delays_late_aircraft",
      "num_of_delays_nas",
      "weather_delay_count"
    ]
]

	airport_code	month	month_num	num_of_delays_weather	num_of_delays_late_aircraft	num_of_delays_nas	weather_delay_count
0	ATL	January	1.0	448	1109.104072	4598	3769.431222
1	DEN	January	1.0	233	928.000000	935	1119.150000
2	IAD	January	1.0	61	1058.000000	895	960.150000
3	ORD	January	1.0	306	2255.000000	5415	4502.250000
4	SAN	January	1.0	56	680.000000	638	674.700000
5	SFO	January	1.0	114	733.000000	1166	1091.800000

QUESTION|TASK 5

Using the new weather variable calculated above, create a barplot showing the proportion of all flights that are delayed by weather at each airport. Describe what you learn from this graph.

From this analysis and graph, we can see the proportion of flights at each airport that are delayed due to the total weather delay count I calculated previously. SFO airport shows the worst here with a 9.79% proportion of dealyed flights due to weather.

df5 = df4.copy()
df5["weather_flag"] = df5["weather_delay_count"] > 0

plot_data = (
    df5
      .groupby("airport_code")
      .agg(
         total_flights        = ("num_of_flights_total", "sum"),
         total_weather_delays = ("weather_delay_count",    "sum")
      )
      .assign(
         prop_weather_delay = lambda d: d["total_weather_delays"] / d["total_flights"]
      )
      .reset_index()
)

from lets_plot import ggplot, aes, geom_bar, labs

p5 = (
    ggplot(plot_data, aes(x="airport_code", y="prop_weather_delay"))
    + geom_bar(stat="identity")
    + labs(
        x="Airport",
        y="Proportion of Flights Delayed by Weather",
        title="Weather-Related Delay Proportion by Airport"
    )
)

p5

---

STRETCH QUESTION|TASK 1

Which delay is the worst delay? Create a similar analysis as above for Weahter Delay with: Carrier Delay and Security Delay. Compare the proportion of delay for each of the three categories in a Chart and a Table. Describe your results.

type your results and analysis here

---